﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.GERP.GIMS.MODEL;
using System.Data;
using KPIS.DBM;
using System.Configuration;

namespace KPIS.GERP.GIMS.DAL
{
    public class D_IcmDeliveryNote
    {
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();
        public IList<M_IcmDeliveryNote> GetList()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_IcmDeliveryNote> infoList = new List<M_IcmDeliveryNote>();
            try
            {
                dbManager.Open();
                strSQL = " SELECT dn.icm_dn_seq,icm_dn_ref_no,dn.icm_dn_name "
                        + " FROM icm_delivery_note dn "
                        + " LEFT JOIN icm_trans_master tm  ON dn.icm_dn_seq = tm.icm_dn_seq "
                        + " and dn.record_status <> 'D' and tm.record_status <>'D' "
                        + " WHERE tm.icm_dn_seq is  null ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_IcmDeliveryNote info = new M_IcmDeliveryNote();
                        info.icm_dn_seq = Convert.ToInt32(drc[i]["icm_dn_seq"]);
                        info.icm_dn_ref_no = drc[i]["icm_dn_ref_no"].ToString();
                        info.icm_dn_name = drc[i]["icm_dn_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        public IList<M_IcmDeliveryNote> GetListFillByName(string icm_dn_ref_no)
        {
            string strSQL = "";
            DataRowCollection drc = null;
            IList<M_IcmDeliveryNote> infoList = new List<M_IcmDeliveryNote>();
            try
            {
                dbManager.Open();
                strSQL = " SELECT dn.icm_dn_seq,icm_dn_ref_no,dn.icm_dn_name "
                        + " FROM icm_delivery_note dn "
                        + " LEFT JOIN icm_trans_master tm  ON dn.icm_dn_seq = tm.icm_dn_seq "
                        + " and dn.record_status <> 'D' and tm.record_status <>'D' "
                        + " WHERE tm.icm_dn_seq is  null  and icm_dn_ref_no LIKE '%' '" + icm_dn_ref_no + "'  '%'";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_IcmDeliveryNote info = new M_IcmDeliveryNote();
                        info.icm_dn_seq = Convert.ToInt32(drc[i]["icm_dn_seq"]);
                        info.icm_dn_ref_no = drc[i]["icm_dn_ref_no"].ToString();
                        info.icm_dn_name = drc[i]["icm_dn_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public M_IcmDeliveryNote GetListFillBySeq(Nullable<Int32> icm_dn_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_IcmDeliveryNote info = new M_IcmDeliveryNote();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@icm_dn_seq", icm_dn_seq);

                strSQL = " SELECT dn.icm_dn_seq,icm_dn_ref_no,dn.icm_dn_name "
                        + " FROM icm_delivery_note dn "
                        + " LEFT JOIN icm_trans_master tm  ON dn.icm_dn_seq = tm.icm_dn_seq "
                        + " and dn.record_status <> 'D' and tm.record_status <>'D' "
                        + " WHERE tm.icm_dn_seq is  null  and dn.icm_dn_seq = @icm_dn_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.icm_dn_seq = Convert.ToInt32(drc[0]["icm_dn_seq"]);
                    info.icm_dn_ref_no = drc[0]["icm_dn_ref_no"].ToString();
                    info.icm_dn_name = drc[0]["icm_dn_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }
        //**** Noise Add Dataset ***************
        public DataSet GetDnApprove()
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@approve_status", SystemInfo._ApproveRecordStatus);

            string sql = "SELECT dn.icm_dn_seq"
                        + " ,dn.icm_dn_ref_no"
                        + " ,dn.icm_dn_name"
                        + " ,dn.icm_po_seq"
                        + " FROM icm_delivery_note dn"
                        + " where dn.record_status = @approve_status"; 
            

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
        public DataSet GetItemByDn(string dn_seq, string item_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(3);
            dbManager.AddParameters(0,"@dn_seq", dn_seq.Length > 0 ? int.Parse(dn_seq) : (int?)null);
            dbManager.AddParameters(1, "@item_seq", item_seq.Length > 0 ? int.Parse(item_seq) : (int?)null);
            dbManager.AddParameters(2, "@approve_status", SystemInfo._ApproveRecordStatus);

            string sql = "SELECT dn.icm_dn_ref_no"
                        + " ,dn.icm_dn_name"
                        + " ,dn.icm_po_seq"
                        + " ,dn.icm_dn_seq"
                        + " ,dn.receive_date"
                        + " ,dn.record_status"
                        + " ,di.item_qty"
                        + " ,di.item_price_include_vat"
                        + " ,po.icm_po_ref_no"
                        + " ,po.icm_bidder_seq"
                        + " ,po.record_status"
                        + " ,pr.icm_pr_seq"
                        + " ,pr.record_status"
                        + " ,ri.icm_res_item_seq"
                        + " ,mr.icm_res_item_name"
                        + " ,mr.icm_res_item_code"
                        + " ,mr.icm_res_item_descr"
                        + " FROM icm_delivery_note dn"
                        + " left Join icm_delivery_note_item di ON dn.icm_dn_seq = di.icm_dn_seq"
                        + " left Join icm_purchase_order po ON dn.icm_po_seq = po.icm_po_seq"
                        + " left Join icm_bidder bd ON po.icm_bidder_seq = bd.icm_bidder_seq"
                        + " left Join icm_purchase_request pr ON bd.icm_pr_seq = pr.icm_pr_seq"
                        + " left Join icm_purchase_request_item  ri ON  pr.icm_pr_seq = ri.icm_pr_seq"
                        + " left Join icm_mas_resource_item mr ON ri.icm_res_item_seq = mr.icm_res_item_seq"
                        + " where dn.record_status = @approve_status"
                        + (dn_seq != null ? " AND dn.icm_dn_seq = @dn_seq " : "")
                        + (item_seq != "" ? " AND ri.icm_res_item_seq = @item_seq " : "");                        

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
    }
}
